NamedParameterJdbcTemplate学习总结

环境

版本信息

spring的版本为4.1.4(spring3我也用过，就配置信息略有不同，其用法还是一样的)

配置信息

需要在applicationContext.xml中配置以下信息

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dataSource"/>
</bean>

如果已经在文件中配置了jdbctemplate的话还可以使用以下方法配置

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="jdbcTemplate"/>
</bean>

当然也可以使用最原始的方法(该方法也需要配置jdbctemplate)

NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

持久化类

本文章中的所有案例都是根据以下类来试验的:

    public class User {
        private int id;
        private String userName;
        private String sex;
        private String password;
        private String address;
            //省略 set/get
    }

简单操作数据

查询

查询一条数据

传入参数是基本数据类型的map时

    public User selectUserById(String id) {
        String sql = "SELECT id,username,sex,password,address FROM user WHERE id = :id";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("id", id);
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        User user = namedParameterJdbcTemplate.queryForObject(sql, paramMap, rm);
        return user;
    }

传入的参数是对象时

    public User queryByUser(User user) {
        String sql = "SELECT id,username,sex,password,address FROM user WHERE id = :id";
        SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        return namedParameterJdbcTemplate.queryForObject(sql, ps, rm);
    }

查询多条数据

普通查询

    public List<User> selectUser() {
        String sql = "SELECT id,username,sex,password,address FROM user";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        List<User> userList = namedParameterJdbcTemplate.query(sql, rm);
        return userList;
    }

模糊查询

    public List<User> selectUserLikeByName(String name) {
        //'%'空格:userName空格'%' 一定要有空格，不然会报错
        String sql = "SELECT id,username,sex,password,address FROM user WHERE username LIKE '%' :userName '%'";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("userName", name);
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        List<User> users = namedParameterJdbcTemplate.query(sql, paramMap, rm);
        return users;
    }

添加数据

    public void insertUser(User user) {
        String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("username", user.getUserName());
        paramMap.put("sex", user.getSex());
        paramMap.put("password", user.getPassword());
        paramMap.put("address", user.getAddress());
        namedParameterJdbcTemplate.update(sql, paramMap);
    }

修改数据

    public void updateUser(User user) {
        String sql = "UPDATE user SET username = :userName,sex = :sex,password=:password,address=:address WHERE id = :id;";
        SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
        namedParameterJdbcTemplate.update(sql, ps);
    }

可以与添加数据再进行对比，这样就能发现当占位符比较多的情况下传入对象时多么的方便

删除数据

    public void deleteUser(String id) {
        String sql = "DELETE FROM user WHERE id = :id";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("id", id);
        namedParameterJdbcTemplate.update(sql, paramMap);
    }

看到此处可以发现添加，修改，删除数据namedParameterJdbcTemplate提供的方法都是一样的，都是update方法。

批量操作数据

批量操作数据一共有两种方法

批量添加

方法一

    public void batchInsert() {
        User chen = new User("chen", "男", "123456789", "fuzhou");
        User alex = new User("alex", "男", "123456", "fuzhou");
        List<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        List<Map<String, Object>> batchValues = new ArrayList<>(userList.size());
        for (User user : userList) {
            batchValues.add(
                    new MapSqlParameterSource("username", user.getUserName())
                            .addValue("sex", user.getSex())
                            .addValue("password", user.getPassword())
                            .addValue("address", user.getAddress())
                            .getValues());
        }
        String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
        int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[userList.size()]));
    }

方法二

    public void batchInsertUser() {
        User chen = new User("chen", "男", "123456789", "fuzhou");
        User alex = new User("alex", "男", "123456", "fuzhou");
        List<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(userList.toArray());
        int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

批量修改

方法一

    public void batchUpdate() {
        User chen = new User(12,"chen", "男", "111111111", "fuzhou");
        User alex = new User(13,"alex", "男", "111111111", "fuzhou");
        List<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        List<Map<String, Object>> batchValues = new ArrayList<>(userList.size());
        for (User user : userList) {
            batchValues.add(
                    new MapSqlParameterSource("username", user.getUserName())
                            .addValue("id",user.getId())
                            .addValue("sex", user.getSex())
                            .addValue("password", user.getPassword())
                            .addValue("address", user.getAddress())
                            .getValues());
        }
        String sql = "UPDATE user SET username = :username,sex = :sex,password=:password,address=:address WHERE id = :id;";
        int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[userList.size()]));
    }

方法二

    public void batchUpdateUser() {
        User chen = new User(14,"chen", "男", "111111111", "fuzhou");
        User alex = new User(15,"alex", "男", "111111111", "fuzhou");
        List<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        String sql = "UPDATE user SET username = :username,sex = :sex,password=:password,address=:address WHERE id = :id;";
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(userList.toArray());
        int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

批量删除

同上